package com.nxy.cpfsfe.busi.bbatproc.chka;

import com.adtec.starring.exception.BaseException;
import com.adtec.starring.util.SpringUtil;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.nxy.cpfsfe.cache.DbToParmToCache;
import com.nxy.cpfsfe.enumtype.Constant;
import com.nxy.cpfsfe.enumtype.MsgCdOrTpEnum;
import com.nxy.cpfsfe.enumtype.RespCd;
import com.nxy.cpfsfe.enumtype.SysEcodeEnum;
import com.nxy.cpfsfe.util.DateUtil;
import com.nxy.cpfsfe.util.Encry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;

import javax.sql.DataSource;
import java.io.File;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class NCSChkAComponent {
	
	private static final Logger LOGGER = LoggerFactory.getLogger(NCSChkAComponent.class);

	private static final String JDBC_DRIVER= "com.mysql.jdbc.Driver";
	private static final String WZ_FLAG = "12";
    private static final String LZ_FLAG = "21";

    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public NCSChkAComponent(){}

    public NCSChkAComponent(String checkBankNo,String pCId,String clrgD){
        initConnection(checkBankNo,pCId,clrgD);
    }

    /**
     *  清空汇兑明细表
     */
    public void truncateRmDetail(String tableName,String checkBankNo,String pCId,String clrgD){

        try{
            String createSql = "CREATE TABLE IF NOT EXISTS " + tableName + " ( " +
                                "`id` bigint(20) NOT NULL AUTO_INCREMENT," +
                                "`msgNo` varchar(35) NOT NULL COMMENT '报文编号'," +
                                "`bizTyp` varchar(4) DEFAULT NULL COMMENT '业务类型'," +
                                "`bizKnd` varchar(5) DEFAULT NULL COMMENT '业务种类'," +
                                "`msgId` varchar(35) NOT NULL COMMENT '报文标识号'," +
                                "`sndBrchNo` varchar(14) DEFAULT NULL COMMENT '发起行行号'," +
                                "`sndBNo` varchar(14) DEFAULT NULL COMMENT '发起成员行行号'," +
                                "`xId` varchar(35) DEFAULT NULL COMMENT '交易标识号'," +
                                "`endToEndId` varchar(35) DEFAULT NULL COMMENT '端到端标识号'," +
                                "`sndTm` datetime DEFAULT NULL COMMENT '报文发送时间'," +
                                "`xCTyp` varchar(8) DEFAULT NULL COMMENT '交易渠道类型'," +
                                "`sysNo` varchar(4) DEFAULT NULL COMMENT '系统编号'," +
                                "`pyrNm` varchar(120) DEFAULT NULL COMMENT '付款人名称'," +
                                "`pyrAddr` varchar(140) DEFAULT NULL COMMENT '付款人地址'," +
                                "`pyrOpenABkNo` varchar(14) DEFAULT NULL COMMENT '付款人开户行行号'," +
                                "`pyrA` varchar(32) DEFAULT NULL COMMENT '付款人账号'," +
                                "`pyrBrchNo` varchar(14) DEFAULT NULL COMMENT '付款行行号'," +
                                "`pyeNm` varchar(120) DEFAULT NULL COMMENT '收款人名称'," +
                                "`pyeAddr` varchar(140) DEFAULT NULL COMMENT '收款人地址'," +
                                "`pyeOpenABkNo` varchar(14) DEFAULT NULL COMMENT '收款人开户行行号'," +
                                "`pyeA` varchar(32) DEFAULT NULL COMMENT '收款人账号'," +
                                "`pyeBrchNo` varchar(14) DEFAULT NULL COMMENT '收款行行号'," +
                                "`curCd` varchar(4) DEFAULT NULL COMMENT '货币代码'," +
                                "`amt` decimal(18,2) DEFAULT NULL COMMENT '金额'," +
                                "`fe` decimal(18,2) DEFAULT NULL COMMENT '手续费'," +
                                "`rmk` varchar(270) DEFAULT NULL COMMENT '备注'," +
                                "`purp` varchar(270) DEFAULT NULL COMMENT '用途'," +
                                "`orgnSndBrchNo` varchar(35) DEFAULT NULL COMMENT '原发起行行号'," +
                                "`orgnEntstAmt` decimal(18,2) DEFAULT NULL COMMENT '原托金额'," +
                                "`orgnMsgId` varchar(35) DEFAULT NULL COMMENT '原报文标识号'," +
                                "`orgnMsgNo` varchar(35) DEFAULT NULL COMMENT '原报文编号'," +
                                "`rfndRsn` varchar(210) DEFAULT NULL COMMENT '退汇原因'," +
                                "`exceAmt` decimal(18,2) DEFAULT NULL COMMENT '多付金额'," +
                                "`belgPerd` varchar(16) DEFAULT NULL COMMENT '所属期间'," +
                                "`rfusAmt` decimal(18,2) DEFAULT NULL COMMENT '拒付金额'," +
                                "`pAmt` decimal(18,2) DEFAULT NULL COMMENT '支付金额'," +
                                "`clctUntSer` varchar(35) DEFAULT NULL COMMENT '收费单位流水号'," +
                                "`clctCmt` varchar(270) DEFAULT NULL COMMENT '收费附言'," +
                                "`bilNo` varchar(32) DEFAULT NULL COMMENT '票据号码'," +
                                "`bilD` date DEFAULT NULL COMMENT '票据日期'," +
                                "`bilKnd` varchar(2) DEFAULT NULL COMMENT '票据种类'," +
                                "`pyFeTyp` varchar(4) DEFAULT NULL COMMENT '缴费类型'," +
                                "`cpstAmt` decimal(18,2) DEFAULT NULL COMMENT '赔偿金额'," +
                                "`clrgD` date DEFAULT NULL COMMENT '清算日期'," +
                                "`respCd` varchar(8) DEFAULT NULL COMMENT '响应码'," +
                                "`bizRfusInfo` varchar(210) DEFAULT NULL COMMENT '业务拒绝信息'," +
                                "`bizSta` varchar(4) DEFAULT NULL COMMENT '业务状态'," +
                                "`rcvBNo` varchar(14) DEFAULT NULL COMMENT '接收成员行行号'," +
                                "`rcvBrchNo` varchar(14) DEFAULT NULL COMMENT '接收行行号'," +
                                "`contFlg` char(2) NOT NULL COMMENT '来往账标识'," +
                                "PRIMARY KEY (`id`)," +
                                "UNIQUE KEY `uk_chka_rm_msgSeqNo` (`msgId`,`msgNo`,`contFlg`)" +
                               ") ENGINE=InnoDB DEFAULT CHARSET=utf8";

            String truncateSql = "TRUNCATE TABLE " + tableName;

            jdbcTemplate.update(createSql);

            jdbcTemplate.update(truncateSql);
        }catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，清空汇兑明细表[%s]失败", checkBankNo,pCId,clrgD,tableName);
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     * 汇兑明细入库
     */
    public int loadRmDetail(String tableName,String checkBankNo,String pCId,String clrgD,File cdttFile){

        try{
            String sql = "LOAD DATA LOCAL INFILE ? INTO TABLE " + tableName + " FIELDS TERMINATED BY '|' LINES TERMINATED BY '\\n' " +
                         "(msgNo,bizTyp,bizKnd,msgId,sndBrchNo,sndBNo,xId,endToEndId,sndTm,xCTyp,sysNo,pyrNm,pyrAddr,pyrOpenABkNo," +
                         "pyrA,pyrBrchNo,pyeNm,pyeAddr,pyeOpenABkNo,pyeA,pyeBrchNo,curCd,amt,fe,rmk,purp,orgnSndBrchNo,orgnEntstAmt," +
                         "orgnMsgId,orgnMsgNo,rfndRsn,exceAmt,belgPerd,rfusAmt,pAmt,clctUntSer,clctCmt,bilNo,bilD,bilKnd,pyFeTyp," +
                         "cpstAmt,clrgD,respCd,bizRfusInfo,bizSta,rcvBNo,rcvBrchNo,contFlg)";

            return jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,cdttFile.getAbsolutePath());
                }
            });
        }catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，对账文件[%s]入库失败", checkBankNo,pCId,clrgD,cdttFile.getAbsolutePath());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     *  清空借贷记明细表
     */
    public void truncateDrDetail(String tableName,String checkBankNo,String pCId,String clrgD){

        try{
            String createSql = "CREATE TABLE IF NOT EXISTS " + tableName + " ( " +
                                "`id` bigint(20) NOT NULL AUTO_INCREMENT," +
                                "`msgNo` varchar(35) NOT NULL COMMENT '报文编号'," +
                                "`bizTyp` varchar(4) DEFAULT NULL COMMENT '业务类型'," +
                                "`bizKnd` varchar(5) DEFAULT NULL COMMENT '业务种类'," +
                                "`msgId` varchar(35) NOT NULL COMMENT '报文标识号'," +
                                "`sndBrchNo` varchar(14) DEFAULT NULL COMMENT '发起行行号'," +
                                "`orgnMsgId` varchar(35) DEFAULT NULL COMMENT '原报文标识号'," +
                                "`sndTm` datetime DEFAULT NULL COMMENT '报文发送时间'," +
                                "`dtlBizTotDN` int(11) DEFAULT NULL COMMENT '明细业务总笔数'," +
                                "`dtlId` varchar(35) DEFAULT NULL COMMENT '明细标识号'," +
                                "`endToEndId` varchar(35) DEFAULT NULL COMMENT '端到端标识号'," +
                                "`msgHeadRmk` varchar(270) DEFAULT NULL COMMENT '报文头备注'," +
                                "`xCTyp` varchar(8) DEFAULT NULL COMMENT '交易渠道类型'," +
                                "`pyrNm` varchar(120) DEFAULT NULL COMMENT '付款人名称'," +
                                "`pyrAddr` varchar(140) DEFAULT NULL COMMENT '付款人地址'," +
                                "`pyrOpenABkNo` varchar(14) DEFAULT NULL COMMENT '付款人开户行行号'," +
                                "`pyrA` varchar(32) DEFAULT NULL COMMENT '付款人账号'," +
                                "`pyrATyp` varchar(4) DEFAULT NULL COMMENT '付款人账户类型'," +
                                "`pyrBNo` varchar(14) DEFAULT NULL COMMENT '付款人成员行行号'," +
                                "`pyrBrchNo` varchar(14) DEFAULT NULL COMMENT '付款行行号'," +
                                "`pyeNm` varchar(120) DEFAULT NULL COMMENT '收款人名称'," +
                                "`pyeAddr` varchar(140) DEFAULT NULL COMMENT '收款人地址'," +
                                "`pyeOpenABkNo` varchar(14) DEFAULT NULL COMMENT '收款人开户行行号'," +
                                "`pyeA` varchar(32) DEFAULT NULL COMMENT '收款人账号'," +
                                "`pyeATyp` varchar(4) DEFAULT NULL COMMENT '收款人账户类型'," +
                                "`pyeBNo` varchar(14) DEFAULT NULL COMMENT '收款人成员行行号'," +
                                "`pyeBrchNo` varchar(14) DEFAULT NULL COMMENT '收款行行号'," +
                                "`curCd` varchar(4) DEFAULT NULL COMMENT '货币代码'," +
                                "`amt` decimal(18,2) DEFAULT NULL COMMENT '金额'," +
                                "`rmk` varchar(270) DEFAULT NULL COMMENT '备注'," +
                                "`cmt` varchar(240) DEFAULT NULL COMMENT '附言'," +
                                "`pbBal` decimal(18,2) DEFAULT NULL COMMENT '存折余额'," +
                                "`pbNum` varchar(32) DEFAULT NULL COMMENT '存折号码'," +
                                "`crtMeth` varchar(4) DEFAULT NULL COMMENT '认证方式'," +
                                "`crdlsNum` varchar(32) DEFAULT NULL COMMENT '证件号码'," +
                                "`crdlsTyp` varchar(2) DEFAULT NULL COMMENT '证件类型'," +
                                "`agntBkFe` decimal(18,2) DEFAULT NULL COMMENT '代理行手续费'," +
                                "`bizRespSta` varchar(4) DEFAULT NULL COMMENT '业务回执状态'," +
                                "`bizRfusRsn` varchar(210) DEFAULT NULL COMMENT '业务拒绝原因'," +
                                "`bizRfusCd` varchar(4) DEFAULT NULL COMMENT '业务拒绝码'," +
                                "`respCd` varchar(8) DEFAULT NULL COMMENT '响应码'," +
                                "`clrgD` date DEFAULT NULL COMMENT '清算日期/终态日期'," +
                                "`bizSta` varchar(4) DEFAULT NULL COMMENT '业务状态'," +
                                "`orgnMsgNo` varchar(35) DEFAULT NULL COMMENT '原报文编号'," +
                                "`orgnSndBrchNo` varchar(35) DEFAULT NULL COMMENT '原发起行行号'," +
                                "`icCrdCondCd` char(1) DEFAULT NULL COMMENT 'IC卡条件码'," +
                                "`tReadCpbl` char(1) DEFAULT NULL COMMENT '终端读取能力'," +
                                "`icCrdIdfyFlg` char(1) DEFAULT NULL COMMENT 'IC卡验证可靠行标志'," +
                                "`panInputMeth` varchar(2) DEFAULT NULL COMMENT 'PAN输入方式'," +
                                "`crdSerNo` varchar(3) DEFAULT NULL COMMENT '卡序列号'," +
                                "`contFlg` char(2) NOT NULL COMMENT '来往账标识'," +
                                "PRIMARY KEY (`id`)," +
                                "UNIQUE KEY `uk_chka_dr_msgSeqNo` (`msgId`,`msgNo`,`contFlg`)" +
                                ") ENGINE=InnoDB DEFAULT CHARSET=utf8";

            String truncateSql = "TRUNCATE TABLE " + tableName;

            jdbcTemplate.update(createSql);

            jdbcTemplate.update(truncateSql);
        }catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，清空借贷记明细表[%s]失败", checkBankNo,pCId,clrgD,tableName);
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     *  借贷记明细入库
     */
    public int loadDrDetail(String tableName,String checkBankNo,String pCId,String clrgD,File rtcdFile){

        try{
            String sql = "LOAD DATA LOCAL INFILE ? INTO TABLE " + tableName + " FIELDS TERMINATED BY '|' LINES TERMINATED BY '\\n' " +
                         "(msgNo,bizTyp,bizKnd,msgId,sndBrchNo,orgnMsgId,sndTm,dtlBizTotDN,dtlId,endToEndId,msgHeadRmk,xCTyp,pyrNm," +
                         "pyrAddr,pyrOpenABkNo,pyrA,pyrATyp,pyrBNo,pyrBrchNo,pyeNm,pyeAddr,pyeOpenABkNo,pyeA,pyeATyp,pyeBNo,pyeBrchNo," +
                         "curCd,amt,rmk,cmt,pbBal,pbNum,crtMeth,crdlsNum,crdlsTyp,agntBkFe,bizRespSta,bizRfusRsn,bizRfusCd,respCd," +
                         "clrgD,bizSta,orgnMsgNo,orgnSndBrchNo,icCrdCondCd,tReadCpbl,icCrdIdfyFlg,panInputMeth,crdSerNo,contFlg)";

            return jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,rtcdFile.getAbsolutePath());
                }
            });
        }catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，对账文件[%s]入库失败", checkBankNo,pCId,clrgD,rtcdFile.getAbsolutePath());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     *  对账-更新账务流水
     */
	public void updateTbaSta(String pCId,String checkBankNo,String checkDate,String clrgD) {

        try{
            updateConsistent(checkBankNo,checkDate,clrgD,pCId);
            suppMoreNcs(checkBankNo,checkDate,clrgD,pCId);
            updateConsistentLz(checkBankNo,checkDate,clrgD,pCId);
            updateFailCpfs(checkBankNo,checkDate,clrgD,pCId);
            updateCollectionLz(checkBankNo,checkDate,clrgD,pCId);
            updateReceiptEachLz(checkBankNo,checkDate,clrgD,pCId);
            updateRfndLz(checkBankNo,checkDate,clrgD,pCId);
            regErr(checkBankNo,clrgD,pCId);
        }catch(BaseException e){
            throw e;
        }catch(Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，更新账务流水出现未知异常，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     *  对账-更新支付流水
     */
    public void updatePSta(String pCId,String checkBankNo,String checkDate,String clrgD){

        try{
            doUpdatePSta(checkBankNo,checkDate,clrgD,pCId);
        }catch (BaseException e){
            throw e;
        }catch(Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，更新支付流水出现未知异常，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    private void initConnection(String checkBankNo,String pCId,String clrgD){

	    try{
            String jdbcConfig = DbToParmToCache.getToParm().get("bno_jdbc_mapper/" + checkBankNo).getDataVal();
            String[] jdbcParams = jdbcConfig.split(",");

            String url = "jdbc:mysql://" + jdbcParams[0] + "?useUnicode=true&characterEncoding=utf-8";
            String user = jdbcParams[1];
            String passwd = Encry.decryptString(jdbcParams[2]);

            dataSource = new ComboPooledDataSource();
            ((ComboPooledDataSource)dataSource).setDriverClass(JDBC_DRIVER);
            ((ComboPooledDataSource)dataSource).setJdbcUrl(url);
            ((ComboPooledDataSource)dataSource).setUser(user);
            ((ComboPooledDataSource)dataSource).setPassword(passwd);

            jdbcTemplate = new JdbcTemplate(dataSource);

            LOGGER.info("行号[{}]直连数据库[{}]进行NCS对账",checkBankNo,url);
        }catch(Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，初始化直连数据库连接失败，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     *  更新一致的账务流水
     */
    private void updateConsistent(String checkBankNo,String checkDate,String clrgD,String pCId){

        //汇兑
        String hdSql = "update tb_a tba,td_chka_rm_" + checkBankNo + " rm " +
                       "set tba.bizSta = '00',tba.respSta = 'PR00',tba.chkAD = ?,tba.chkASta = '1',tba.updTm = ?,tba.reserved1 = rm.rmk,tba.reserved2 = rm.orgnMsgId " +
                       "where tba.acqOrgId = ? and tba.cpfsD = ? and tba.pCId = ? and tba.chkASta = '0' and tba.chlSeqNo = rm.msgId and tba.sndOrgId = rm.sndBNo";

        //借贷记
        String jdjSql = "update tb_a tba,td_chka_dr_" + checkBankNo + " dr " +
                        "set tba.bizSta = '00',tba.respSta = 'PR00',tba.chkAD = ?,tba.chkASta = '1',tba.updTm = ?,tba.reserved3 = dr.pyrATyp " +
                        "where tba.acqOrgId = ? and tba.cpfsD = ? and tba.pCId = ? and tba.chkASta = '0' and tba.chlSeqNo = dr.msgId and tba.sndOrgId = dr.sndBrchNo";

        //日间多来账的情况
        String errLzSql = "update tb_a tba " +
                          "set tba.adjAFlg = '1',tba.chkASta = '3',tba.updTm = ? " +
                          "where tba.acqOrgId = ? and tba.cpfsD = ? and tba.pCId = ? and tba.chkASta = '1' and tba.xDirn = '" + LZ_FLAG + "' " +
                          "and not exists (select 1 from ts_p_cmd tspcmd where tspcmd.xSeqNo = tba.reserved1 and tspcmd.sndBNo = tba.rcvOrgId and tspcmd.acqOrgId = tba.acqOrgId)";

        String curTime = DateUtil.getDateTime();
        try{
            long startTm = System.currentTimeMillis();
            int count = jdbcTemplate.update(hdSql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,checkDate);
                    preparedStatement.setString(2,curTime);
                    preparedStatement.setString(3,checkBankNo);
                    preparedStatement.setString(4,clrgD);
                    preparedStatement.setString(5,pCId);
                }
            });
            long endTm = System.currentTimeMillis();
            LOGGER.info("更新[{}]条一致的汇兑账务流水耗时：[{}]ms",count,(endTm - startTm));

            startTm = System.currentTimeMillis();
            count = jdbcTemplate.update(jdjSql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,checkDate);
                    preparedStatement.setString(2,curTime);
                    preparedStatement.setString(3,checkBankNo);
                    preparedStatement.setString(4,clrgD);
                    preparedStatement.setString(5,pCId);
                }
            });
            endTm = System.currentTimeMillis();
            LOGGER.info("更新[{}]条一致的借贷记账务流水耗时：[{}]ms",count,(endTm - startTm));

            startTm = System.currentTimeMillis();
            count = jdbcTemplate.update(errLzSql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,curTime);
                    preparedStatement.setString(2,checkBankNo);
                    preparedStatement.setString(3,clrgD);
                    preparedStatement.setString(4,pCId);
                }
            });
            endTm = System.currentTimeMillis();
            LOGGER.info("更新[{}]条一致的来账流水中非流水号登记对应的来账流水耗时：[{}]ms",count,(endTm - startTm));
        }catch(Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，更新一致的账务流水失败，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     *  补记NCS多的账务流水
     */
    private void suppMoreNcs(String checkBankNo,String checkDate,String clrgD,String pCId){

        String curTime = DateUtil.getDateTime();

        //补记汇兑
        String hdInsertSql = "insert into tb_a(cpfsSeqNo,cpfsD,pNo,shardingId,acqOrgId,drcrFlg,bizTyp,bizCls,sndSysId,sndOrgId," +
                                              "rcvSysId,rcvOrgId,xDirn,pCId,chlXCd,chlSeqNo,endToEndId,xAmt,feAmt," +
                                              "curTyp,pyrBNo,pyrBrchNo,pyrA,pyrNm,pyeBNo,pyeBrchNo,pyeA,pyeNm," +
                                              "bizSta,respSta,chkAD,adjAFlg,chkASta,reserved1,reserved2,creTm) " +
                             "select msgId,clrgD,'0' pNo,'" + checkBankNo + "' shardingId,'" + checkBankNo + "' acqOrgId,'2' drcrFlg,bizTyp,bizKnd," +
                                    "case contFlg when '0' then 'UFO' when '1' then 'NCS' end sndSysId,sndBNo," +
                                    "case contFlg when '0' then 'NCS' when '1' then 'UFO' end rcvSysId,rcvBNo," +
                                    "case contFlg when '0' then '" + WZ_FLAG + "' when '1' then '" + LZ_FLAG + "' end xDirn," +
                                    "'NCS' pCId,msgNo,msgId chlSeqNo,endToEndId,amt,fe,curCd," +
                                    "sndBNo pyrBNo,pyrBrchNo,pyrA,pyrNm,rcvBNo pyeBNo,pyeBrchNo,pyeA,pyeNm," +
                                    "'00' bizSta,bizSta respSta,'" + checkDate + "' chkAD,'1' adjAFlg,'3' chkASta," +
                                    "rmk reserved1,orgnMsgId reserved2,'" + curTime + "' creTm " +
                             "from td_chka_rm_" + checkBankNo + " rm " +
                             "where not exists(select 1 from tb_a tba " +
                                               "where tba.acqOrgId = ? and tba.cpfsD = ? and tba.pCId = ? and tba.chlSeqNo = rm.msgId and tba.sndOrgId = rm.sndBNo)";

        //补记借贷记
        String jdjInsertSql = "insert into tb_a(cpfsSeqNo,cpfsD,pNo,shardingId,acqOrgId,drcrFlg,bizTyp,bizCls,sndSysId,sndOrgId," +
                                               "rcvSysId,rcvOrgId,xDirn,pCId,chlXCd,chlSeqNo,endToEndId,xAmt,feAmt," +
                                               "curTyp,pyrBNo,pyrBrchNo,pyrA,pyrNm,pyeBNo,pyeBrchNo,pyeA,pyeNm," +
                                               "bizSta,respSta,chkAD,adjAFlg,chkASta,reserved1,reserved3,creTm) " +
                              "select msgId,clrgD,'0' pNo,'" + checkBankNo + "' shardingId,'" + checkBankNo + "' acqOrgId,case msgNo when '" + MsgCdOrTpEnum.MSGCD_142.getCode() + "' then '1' when '" + MsgCdOrTpEnum.MSGCD_140.getCode() + "' then '2' end drcrFlg," +
                                     "bizTyp,bizKnd,'UFO' sndSysId,sndBrchNo sndOrgId,'NCS' rcvSysId,case msgNo when '" + MsgCdOrTpEnum.MSGCD_142.getCode() + "' then pyrBNo when '" + MsgCdOrTpEnum.MSGCD_140.getCode() + "' then pyeBNo end rcvOrgId," +
                                     "'" + WZ_FLAG + "' xDirn,'NCS' pCId,msgNo,msgId chlSeqNo,endToEndId,amt,agntBkFe,curCd,pyrBNo,pyrBrchNo,pyrA,pyrNm,pyeBNo,pyeBrchNo,pyeA,pyeNm,'00' bizSta,bizSta respSta,'" + checkDate + "' chkAD," +
                                     "'1' adjAFlg,'3' chkASta,rmk reserved1,pyrATyp reserved3,'" + curTime + "' creTm " +
                              "from td_chka_dr_" + checkBankNo + " dr " +
                              "where not exists(select 1 from tb_a tba " +
                                                "where tba.acqOrgId = ? and tba.cpfsD = ? and tba.pCId = ? and tba.chlSeqNo = dr.msgId and tba.sndOrgId = dr.sndBrchNo)";

        try{
            long startTm = System.currentTimeMillis();
            int hdCount = jdbcTemplate.update(hdInsertSql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,checkBankNo);
                    preparedStatement.setString(2,clrgD);
                    preparedStatement.setString(3,pCId);
                }
            });

            int jdjCount = jdbcTemplate.update(jdjInsertSql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,checkBankNo);
                    preparedStatement.setString(2,clrgD);
                    preparedStatement.setString(3,pCId);
                }
            });
            long endTm = System.currentTimeMillis();
            LOGGER.info("补记[{}]条汇兑账务流水和[{}]条借贷记账务流水耗时：[{}]ms",hdCount,jdjCount,(endTm - startTm));
        }catch (DuplicateKeyException e){
            LOGGER.info("无须补录NCS的流水");
        }catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，补记NCS多的账务流水失败，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     *  更新一致的流水号登记来账流水
     */
    private void updateConsistentLz(String checkBankNo,String checkDate,String clrgD,String pCId){

        String sql = "update tb_a tba,ts_p_cmd tspcmd " +
                     "set tba.rcvSysId = tspcmd.bizSysId,tba.cpfsSeqNo = tspcmd.cpfsSeqNo,tba.adjAFlg = '0',tba.chkASta = '1',tba.chkAD = ?,tba.updTm = ?," +
                         "tspcmd.respMsgId = tba.chlSeqNo,tspcmd.ordAmt = tba.xAmt " +
                     "where tba.acqOrgId = ? and tba.cpfsD = ? and tba.pCId = ? and tba.xDirn = '" + LZ_FLAG + "' " +
                           "and tspcmd.xSeqNo = tba.reserved1 and tspcmd.sndBNo = tba.rcvOrgId and tspcmd.acqOrgId = tba.acqOrgId";

        String curTime = DateUtil.getDateTime();
        try{
            long startTm = System.currentTimeMillis();
            int count = jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,checkDate);
                    preparedStatement.setString(2,curTime);
                    preparedStatement.setString(3,checkBankNo);
                    preparedStatement.setString(4,clrgD);
                    preparedStatement.setString(5,pCId);
                }
            });
            long endTm = System.currentTimeMillis();
            LOGGER.info("更新[{}]条一致的流水号登记来账流水耗时：[{}]ms",count/2,(endTm - startTm));
        }catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，更新一致的流水号登记来账流水失败，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     *  更新前置失败的账务流水
     */
    private void updateFailCpfs(String checkBankNo,String checkDate,String clrgD,String pCId){

        String sql = "update tb_a set chkAD = ?,chkASta = '2',bizSta = '01',adjAFlg = '0',updTm = ? " +
                     "where acqOrgId = ? and cpfsD = ? and pCId = ? and chkASta = '0'";

        String curTime = DateUtil.getDateTime();
        try{
            long startTm = System.currentTimeMillis();
            int count = jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,checkDate);
                    preparedStatement.setString(2,curTime);
                    preparedStatement.setString(3,checkBankNo);
                    preparedStatement.setString(4,clrgD);
                    preparedStatement.setString(5,pCId);
                }
            });
            long endTm = System.currentTimeMillis();
            LOGGER.info("更新[{}]条前置失败的账务流水耗时：[{}]ms",count,(endTm - startTm));
        }catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，更新前置失败的账务流水失败，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     * 更新资金归集的来账账务流水
     */
    private void updateCollectionLz(String checkBankNo,String checkDate,String clrgD,String pCId){

        String updateTbASql = "update tb_a " +
                              "set chkAD = ?,updTm = ?,adjAFlg = '0',chkASta = '1',rcvSysId = '" + SysEcodeEnum.CPFS.getCode() + "' " +
                              "where acqOrgId = ? and cpfsD = ? and pCId = ? and chkASta = '3' and xDirn = '" + LZ_FLAG + "' " +
                                    "and reserved1 = '" + Constant.ZJGJ_FLG + "'";

        String curTime = DateUtil.getDateTime();
        try{
            long startTm = System.currentTimeMillis();

            int count = 0;
            count = jdbcTemplate.update(updateTbASql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,checkDate);
                    preparedStatement.setString(2,curTime);
                    preparedStatement.setString(3,checkBankNo);
                    preparedStatement.setString(4,clrgD);
                    preparedStatement.setString(5,pCId);
                }
            });
            long endTm = System.currentTimeMillis();
            LOGGER.info("更新[{}]条资金归集的来账账务流水耗时：[{}]ms",count,(endTm - startTm));
        }catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，更新资金归集的来账账务流水失败，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     * 更新收单行互发的来账账务流水
     */
    private void updateReceiptEachLz(String checkBankNo,String checkDate,String clrgD,String pCId){

        String ngpsOrgId = DbToParmToCache.getToParm().get("bizsys_bno_mapper/NGPS").getDataVal();//老e支付行号
        String nvasOrgId = DbToParmToCache.getToParm().get("bizsys_bno_mapper/NVAS").getDataVal();//老e管家行号

        String orgQuerySql = "select orgBNo from to_org where orgCls != '9'";

        String updateTbASql = "update tb_a " +
                              "set chkAD = ?,updTm = ?,adjAFlg = '0',chkASta = '1'," +
                                  "rcvSysId = case rcvOrgId when '" + ngpsOrgId + "' then '" + SysEcodeEnum.EPAY.getCode() + "' when '" + nvasOrgId + "' then '" + SysEcodeEnum.EMAN.getCode() + "' else '" + SysEcodeEnum.CPFS.getCode() + "' end " +
                              "where acqOrgId = ? and cpfsD = ? and pCId = ? and chkASta = '3' and xDirn = '" + LZ_FLAG + "' " +
                                     "and sndOrgId in (";

        JdbcTemplate ctxJdbcTemplate = (JdbcTemplate) SpringUtil.getBean("jdbcTemplate");
        String curTime = DateUtil.getDateTime();
        try{
            long startTm = System.currentTimeMillis();
            List<String> orgBNos = ctxJdbcTemplate.queryForList(orgQuerySql,String.class);

            int count = 0;
            if(orgBNos != null && orgBNos.size() > 0){
                for (String orgBNo : orgBNos){
                    updateTbASql += "'" + orgBNo + "',";
                }

                updateTbASql = updateTbASql.substring(0,updateTbASql.length() - 1) + ")";

                count = jdbcTemplate.update(updateTbASql, new PreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement preparedStatement) throws SQLException {
                        preparedStatement.setString(1,checkDate);
                        preparedStatement.setString(2,curTime);
                        preparedStatement.setString(3,checkBankNo);
                        preparedStatement.setString(4,clrgD);
                        preparedStatement.setString(5,pCId);
                    }
                });
            }
            long endTm = System.currentTimeMillis();
            LOGGER.info("更新[{}]条收单行互发的来账账务流水耗时：[{}]ms",count,(endTm - startTm));
        }catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，更新收单行互发的来账账务流水失败，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     *  更新退汇来账流水
     */
    private void updateRfndLz(String checkBankNo,String checkDate,String clrgD,String pCId){

        String updateTbASql = "update tb_a tbaA,tb_a tbaB " +
                              "set tbaA.chkAD = ?,tbaA.updTm = ?,tbaA.adjAFlg = '0',tbaA.chkASta = '1',tbaA.rcvSysId = tbaB.sndSysId " +
                              "where tbaA.acqOrgId = ? and tbaA.cpfsD = ? and tbaA.pCId = ? and tbaA.chkASta = '3' and tbaA.xDirn = '" + LZ_FLAG + "' and tbaA.bizTyp = 'A102' " +
                                    "and tbaB.acqOrgId = ? and tbaB.pCId = ? and tbaB.chkASta = '1' and tbaB.xDirn = '" + WZ_FLAG + "' " +
                                    "and tbaA.reserved2 = tbaB.chlSeqNo";

        String curTime = DateUtil.getDateTime();
        try{
            long startTm = System.currentTimeMillis();
            int count = jdbcTemplate.update(updateTbASql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,checkDate);
                    preparedStatement.setString(2,curTime);
                    preparedStatement.setString(3,checkBankNo);
                    preparedStatement.setString(4,clrgD);
                    preparedStatement.setString(5,pCId);
                    preparedStatement.setString(6,checkBankNo);
                    preparedStatement.setString(7,pCId);
                }
            });
            long endTm = System.currentTimeMillis();
            LOGGER.info("更新[{}]条退汇来账流水耗时：[{}]ms",count,(endTm - startTm));
        }catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，更新退汇来账流水失败，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     *  登记差错账务流水
     */
    private void regErr(String checkBankNo,String clrgD,String pCId){

        String curTime = DateUtil.getDateTime();

        String queryErrSql = "select cpfsD,sndSysId,cpfsSeqNo,xDirn,sndOrgId,rcvOrgId,pNo " +
                             "from tb_a " +
                             "where acqOrgId = ? and cpfsD = ? and pCId = ? and chkASta = '3'";

        String insertErrSql = "insert into te_x_err(bizSysD,bizSysId,bizSysSeqNo,xDirn,cpfsD,cpfsSeqNo,sndOrgId,rcvOrgId,pNo,xTyp,updSta,creTm) values";

        JdbcTemplate ctxJdbcTemplate = (JdbcTemplate) SpringUtil.getBean("jdbcTemplate");
        try{
            long startTm = System.currentTimeMillis();
            List<Map<String,Object>> errList = ctxJdbcTemplate.queryForList(queryErrSql,checkBankNo,clrgD,pCId);

            if(errList != null && errList.size() > 0){
                for(Map<String,Object> err : errList){
                    insertErrSql += "('" + err.get("cpfsD") + "','" + err.get("sndSysId") + "','" + err.get("cpfsSeqNo") +
                                    "','" + err.get("xDirn") + "','" + err.get("cpfsD") + "','" + err.get("cpfsSeqNo") +
                                    "','" + err.get("sndOrgId") + "','" + err.get("rcvOrgId") + "','" + err.get("pNo") + "','4','0','" + curTime + "'),";
                }
                insertErrSql = insertErrSql.substring(0,insertErrSql.length() - 1);

                int count = ctxJdbcTemplate.update(insertErrSql);
                long endTm = System.currentTimeMillis();
                LOGGER.info("登记[{}]条差错账务流水耗时：[{}]ms",count,(endTm - startTm));
            }else{
                LOGGER.info("无须登记差错");
            }
        }catch (DuplicateKeyException e){
            LOGGER.info("差错账务流水已登记");
        } catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，登记差错账务流水失败，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

    /**
     *  更新支付流水状态
     */
    private void doUpdatePSta(String checkBankNo,String checkDate,String clrgD,String pCId){

        //支付次数为1的支付流水
        String pTms1Sql = "update ts_p_cmd tspcmd,tb_a tba " +
                          "set tspcmd.cpfsD = ?,tspcmd.chkAD = ?,tspcmd.updTm = ?,tspcmd.chkASta = 1,tspcmd.bizSta = '00' " +
                          "where tspcmd.acqOrgId = ? and tspcmd.pTms = 1 and tspcmd.chkASta = 0 " +
                                "and tba.acqOrgId = ? and tba.cpfsD = ? and tba.pCId = ? and tba.chkASta = '1' " +
                                "and tba.cpfsSeqNo = tspcmd.cpfsSeqNo";

        //支付次数为2的支付流水
        String pTms2Sql = "update ts_p_cmd tspcmd " +
                          "set tspcmd.chkAD = ?,tspcmd.updTm = ?,tspcmd.chkASta = 1,tspcmd.bizSta = '00' " +
                          "where tspcmd.acqOrgId = ? and tspcmd.pTms = 2 " +
                                 "and 2 = (select count(1) from tb_a tba " +
                                          "where tba.acqOrgId = tspcmd.acqOrgId and tba.cpfsD = ? and tba.chkASta = '1' and tba.xDirn = '" + WZ_FLAG + "' " +
                                                 "and tba.cpfsSeqNo = tspcmd.cpfsSeqNo and tba.sndOrgId = tspcmd.sndBNo)";

        //失败的支付流水
        String failSql = "update ts_p_cmd set chkAD = ?,updTm = ?,chkASta = 1,bizSta = '01' " +
                         "where acqOrgId = ? and chkASta = 0 and ((cpfsD = ? and msgTp != '" + MsgCdOrTpEnum.MSGTD_101.getCode() + "') or (cpfsD = date_add(?,interval -6 day) and msgTp = '" + MsgCdOrTpEnum.MSGTD_101.getCode() + "'))";

        String curTime = DateUtil.getDateTime();
        try{
            long startTm = System.currentTimeMillis();

            int s1Count = jdbcTemplate.update(pTms1Sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,clrgD);
                    preparedStatement.setString(2,checkDate);
                    preparedStatement.setString(3,curTime);
                    preparedStatement.setString(4,checkBankNo);
                    preparedStatement.setString(5,checkBankNo);
                    preparedStatement.setString(6,clrgD);
                    preparedStatement.setString(7,pCId);
                }
            });

            int s2Count = jdbcTemplate.update(pTms2Sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,checkDate);
                    preparedStatement.setString(2,curTime);
                    preparedStatement.setString(3,checkBankNo);
                    preparedStatement.setString(4,clrgD);
                }
            });

            int fCount = jdbcTemplate.update(failSql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement) throws SQLException {
                    preparedStatement.setString(1,checkDate);
                    preparedStatement.setString(2,curTime);
                    preparedStatement.setString(3,checkBankNo);
                    preparedStatement.setString(4,clrgD);
                    preparedStatement.setString(5,clrgD);
                }
            });
            long endTm = System.currentTimeMillis();
            LOGGER.info("更新[{}]条成功的[支付次数为1的流水]、[{}]条成功的[支付次数为2的流水]、[{}]条失败的支付流水状态耗时：[{}]ms",s1Count,s2Count,fCount,(endTm - startTm));
        }catch (Exception e){
            String errMsg = String.format("机构[%s]与[%s]通道[%s]日对账失败，更新支付流水状态失败，[%s]", checkBankNo,pCId,clrgD,e.getMessage());
            throw new BaseException(RespCd.NCPO6037,e,errMsg);
        }

    }

}
